﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlServerCe;
using System.Reflection;
using System.Text;


namespace Smart
{
    public class SqlCeClient
    {
        public string CnnStr { get; set; }
        Dictionary<string, TableIndex> Tables = new Dictionary<string, TableIndex>();

        #region Ctor
        private static SqlCeClient _instance = null;
        private SqlCeClient() { }
        public static SqlCeClient Get
        {
            get
            {
                if (_instance == null)
                    _instance = new SqlCeClient();
                return _instance;
            }
        }
        #endregion

        #region Private Methods
        private void AddTable(string tableName, string columnName, string keyName)
        {
            if (Tables.ContainsKey(tableName))
            {
                TableIndex idx = Tables[tableName];
                idx.Columns.Add(columnName);
            }
            else
            {
                TableIndex idx = new TableIndex();
                idx.Columns.Add(columnName);
                idx.TableName = tableName;
                idx.IndexName = keyName;
                Tables.Add(tableName, idx);
            }
        }
        private object[] GetTablePkValues(SqlCeResultSet rs, string[] record, string tableName)
        {
            TableIndex idx = Tables[tableName];
            object[] returnValue = new object[idx.Columns.Count];
            for (int i = 0; i < idx.Columns.Count; i++)
                returnValue[i] = record[rs.GetOrdinal(idx.Columns[i])];
            return returnValue;
        }
        private class TableIndex
        {
            private List<string> columns = new List<string>();
            public string TableName { get; set; }
            public List<string> Columns
            {
                get { return columns; }
            }
            public string IndexName { get; set; }
        }
        private void IndexYukle()
        {
            using (SqlCeConnection cn = new SqlCeConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand("SELECT     TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME  FROM         INFORMATION_SCHEMA.KEY_COLUMN_USAGE  GROUP BY TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, ORDINAL_POSITION  ORDER BY TABLE_NAME, ORDINAL_POSITION", cn))
                {
                    SqlCeDataReader rd = cmd.ExecuteReader();
                    while (rd.Read())
                    {
                        AddTable(rd.GetString(0), rd.GetString(1), rd.GetString(2));
                    }
                    rd.Close();
                }
                cn.Close();
            }
        }
        private object[] GetTablePkValues(SqlCeResultSet rs, object[] record, string tableName)
        {
            TableIndex idx = Tables[tableName];
            object[] returnValue = new object[idx.Columns.Count];
            for (int i = 0; i < idx.Columns.Count; i++)
                returnValue[i] = record[rs.GetOrdinal(idx.Columns[i])];
            return returnValue;
        }
        #endregion

        public IEnumerable<string> importRows(string tableName, string[] records)
        {
            if (records != null)
            {

                if (Tables.Count == 0)
                    IndexYukle();

                using (SqlCeConnection cn = new SqlCeConnection(CnnStr))
                {
                    cn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand(tableName, cn) { CommandType = CommandType.TableDirect })
                    {
                        cmd.IndexName = Tables[tableName].IndexName;

                        using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
                        {
                            for (int i = 0; i < records.Length; i++)
                            {
                                string[] record = records[i].Split('Æ');
                                if (record.Length == rs.FieldCount) //kolon sayıları aynı mı ?
                                {
                                    //TODO:burası sorunlu
                                    for (int c = 0; c < rs.FieldCount; c++) //kolon değer kontrolü
                                    {
                                        if (rs.GetFieldType(c) != typeof(string) && record[c] == string.Empty)
                                            record[c] = "0";
                                        if (rs.GetFieldType(c) == typeof(string) && record[c].ToString().Length > 50)
                                            record[c] = record[c].ToString().Substring(0, 49);
                                    }

                                    if (rs.Seek(DbSeekOptions.FirstEqual, GetTablePkValues(rs, record, tableName)))
                                    {
                                        rs.Read();
                                        rs.SetValues(record);
                                        rs.Update();
                                    }
                                    else
                                    {
                                        SqlCeUpdatableRecord rc = rs.CreateRecord();
                                        rc.SetValues(record);
                                        rs.Insert(rc);
                                    }
                                }
                                else
                                    throw new Exception("Satırlar Aynı Uzunlukta Değil");

                                yield return string.Format("{0}/{1}", i, records.Length);
                            }
                        }
                    }

                    cn.Close();
                }
            }
            yield return "Tamamlandı";
        }

        public void importAllRows(string tableName, string[] records)
        {
            if (records != null)
            {
                if (Tables.Count == 0)
                    IndexYukle();

                using (SqlCeConnection cn = new SqlCeConnection(CnnStr))
                {
                    cn.Open();
                    using (SqlCeCommand cmd = new SqlCeCommand(tableName, cn) { CommandType = CommandType.TableDirect })
                    {
                        cmd.IndexName = Tables[tableName].IndexName;

                        using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
                        {
                            for (int i = 0; i < records.Length; i++)
                            {
                                string[] record = records[i].Split('Æ');
                                if (record.Length == rs.FieldCount) //kolon sayıları aynı mı ?
                                {
                                    //TODO:burası sorunlu
                                    for (int c = 0; c < rs.FieldCount; c++) //kolon değer kontrolü
                                    {
                                        if (rs.GetFieldType(c) != typeof(string) && record[c] == string.Empty)
                                            record[c] = "0";

                                        if (rs.GetFieldType(c) == typeof(decimal))
                                        {
                                            record[c] = record[c].Replace('.', ',');
                                        }
                                        else if (rs.GetFieldType(c) == typeof(string))
                                        {
                                            if (record[c].ToString().Length > 50)
                                                record[c] = record[c].ToString().Substring(0, 49);

                                            record[c] = record[c].RemoveTurkishCharacter();
                                        }
                                    }

                                    if (rs.Seek(DbSeekOptions.FirstEqual, GetTablePkValues(rs, record, tableName)))
                                    {
                                        rs.Read();
                                        rs.SetValues(record);
                                        rs.Update();
                                    }
                                    else
                                    {
                                        SqlCeUpdatableRecord rc = rs.CreateRecord();
                                        rc.SetValues(record);
                                        rs.Insert(rc);
                                    }
                                }
                                else
                                    throw new Exception("Satırlar Aynı Uzunlukta Değil");

                            }
                        }
                    }

                    cn.Close();
                }
            }
        }

        private object[] ToArray(Tablo entity)
        {
            List<object> values = new List<object>();
            foreach (var col in Entity.Get.Tables[entity.TabloAdi].Fields)
            {
                object val = entity.GetType().GetProperty(col.Key).GetValue(entity, null);
                values.Add(val != null ? val : 0);
            }
            return values.ToArray();
        }

        public void insertOrUpdateRecord(Tablo entity, bool deleteRecord)
        {
            if (Tables.Count == 0)
                IndexYukle();

            using (SqlCeConnection cn = new SqlCeConnection(CnnStr))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(entity.TabloAdi, cn) { CommandType = CommandType.TableDirect })
                {
                    cmd.IndexName = string.Format("PK_IDX_{0}_{1}", entity.TabloAdi, "PK");

                    using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable))
                    {
                        object[] values = ToArray(entity);
                        if (rs.Seek(DbSeekOptions.FirstEqual, GetTablePkValues(rs, values, entity.TabloAdi)))
                        {
                            if (!deleteRecord)
                            {
                                rs.Read();
                                rs.SetValues(values);
                                rs.Update();
                            }
                            else
                            {
                                rs.Read();
                                rs.Delete();
                            }
                        }
                        else
                        {
                            SqlCeUpdatableRecord rc = rs.CreateRecord();
                            rc.SetValues(values);
                            rs.Insert(rc);
                        }
                    }
                }
                cn.Close();
            }
        }

        //public IEnumerable<T> ReadResultset<T>(string tableName, string indexName, Operator opert, object[] startValue, object[] endValue)
        //{
        //    using (SqlCeConnection cn = new SqlCeConnection(CnnStr))
        //    {
        //        cn.Open();
        //        using (SqlCeCommand cmd = new SqlCeCommand(tableName, cn) { CommandType = CommandType.TableDirect })
        //        {
        //            if (indexName.IndexOf("PK") == -1)
        //                cmd.IndexName = string.Format("ORM_IDX_{0}_{1}", tableName, indexName);
        //            else
        //                cmd.IndexName = string.Format("PK_IDX_{0}_{1}", tableName, indexName);

        //            DbSeekOptions seekop = DbSeekOptions.FirstEqual;

        //            switch (opert)
        //            {
        //                case Operator.Basinda:
        //                    seekop = DbSeekOptions.AfterEqual;
        //                    cmd.SetRange(DbRangeOptions.InclusiveStart, startValue, null);
        //                    break;
        //                case Operator.Sonuda:
        //                    seekop = DbSeekOptions.BeforeEqual;
        //                    cmd.SetRange(DbRangeOptions.InclusiveEnd, null, startValue);
        //                    break;
        //                case Operator.Arasinda:
        //                    seekop = DbSeekOptions.FirstEqual;
        //                    cmd.SetRange(DbRangeOptions.InclusiveStart | DbRangeOptions.InclusiveEnd, startValue, endValue);
        //                    break;
        //                case Operator.Esittir:
        //                    seekop = DbSeekOptions.FirstEqual;
        //                    cmd.SetRange(DbRangeOptions.Match, startValue, null);
        //                    break;
        //                case Operator.Null:
        //                    seekop = DbSeekOptions.FirstEqual;
        //                    cmd.SetRange(DbRangeOptions.ExcludeNulls, startValue, endValue);
        //                    break;
        //            }

        //            using (SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable))
        //            {
        //                if (rs.Seek(seekop, startValue))
        //                {
        //                    while (rs.Read())
        //                    {
        //                        Type type = typeof(T);
        //                        Type[] argTypes = new Type[] { typeof(SqlCeResultSet) };
        //                        ConstructorInfo cInfo = type.GetConstructor(argTypes);
        //                        T table = (T)cInfo.Invoke(new object[] { rs });
        //                        yield return table;
        //                    }
        //                }
        //            }
        //        }
        //        cn.Close();
        //    }
        //}



        #region yeniden düzenlenenler
        object DefaultValue(string type)
        {
            switch (type)
            {
                case "System.String":
                    return string.Empty;
                case "System.Int32":
                    return 0;
                case "System.Int16":
                    return Convert.ToInt16(0);
                case "System.Int64":
                    return Convert.ToInt64(0);
                case "System.DateTime":
                    return DateTime.Now;
                case "System.Decimal":
                    return Convert.ToDecimal(0);
                case "System.Boolean":
                    return false;
                case "System.Double":
                    return Convert.ToDouble(0);
                case "System.Single":
                    return Convert.ToSingle(0);
                case "System.Byte":
                    return Convert.ToByte(0);
                default:
                    return null;
            }

        }

        public SqlCeTransaction OpenTransaction()
        {
            SqlCeConnection cn = new SqlCeConnection(this.CnnStr);
            cn.Open();
            return cn.BeginTransaction();
        }

        public List<T> ToList<T>(T sample, string sqlText, params SqlCeParameter[] prm)
        {
            List<T> fdList = new List<T>();
            string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
            using (SqlCeConnection cn = new SqlCeConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(sqlText, cn))
                {
                    if (prm != null)
                        cmd.Parameters.AddRange(prm);

                    SqlCeDataReader rd = cmd.ExecuteReader();
                    var properties = TypeDescriptor.GetProperties(sample);
                    Type[] types = new Type[properties.Count];
                    for (int i = 0; i < properties.Count; i++)
                    {
                        types[i] = properties[i].PropertyType;
                    }

                    ConstructorInfo ctor = sample.GetType().GetConstructor(types);
                    object[] objArray = new object[properties.Count];

                    while (rd.Read())
                    {
                        for (int i = 0; i < rd.FieldCount; i++)
                        {
                            objArray[i] = rd.GetValue(i);
                            if (objArray[i] == DBNull.Value)
                                objArray[i] = DefaultValue(rd.GetFieldType(i).ToString());
                        }
                        if (ctor != null)
                        {
                            T tmp = (T)ctor.Invoke(objArray);
                            fdList.Add(tmp);
                        }
                    }
                    rd.Close();
                }
                cn.Close();
            }
            return fdList;
        }

        public void Exec(string sqlText, params SqlCeParameter[] prm)
        {
            using (SqlCeConnection cn = new SqlCeConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(sqlText, cn))
                {
                    if (prm != null)
                        cmd.Parameters.AddRange(prm);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                }
                cn.Close();
            }
        }
        public void Exec(string sqlText)
        {
            Exec(sqlText, null);
        }

        public DataTable Fill(string sqlText, params SqlCeParameter[] prm)
        {
            DataTable dt = new DataTable();
            using (SqlCeConnection cn = new SqlCeConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(sqlText, cn))
                {
                    if (prm != null)
                        cmd.Parameters.AddRange(prm);
                    using (SqlCeDataAdapter adp = new SqlCeDataAdapter(cmd))
                    {
                        adp.Fill(dt);
                        cmd.Parameters.Clear();
                    }
                }
                cn.Close();
            }
            return dt;
        }
        public DataTable Fill(string sqlText)
        {
            return Fill(sqlText, null);
        }

        public T ExecScalar<T>(string sqlText, params SqlCeParameter[] prm)
        {
            object sonuc = null;
            using (SqlCeConnection cn = new SqlCeConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(sqlText, cn))
                {
                    if (prm != null)
                        cmd.Parameters.AddRange(prm);
                    sonuc = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                }
                cn.Close();
            }
            if (sonuc != DBNull.Value)
                return (T)Convert.ChangeType(sonuc, typeof(T), null);
            else
                return (T)Cevir.DefaultSetting(typeof(T).ToString());
        }
        public T ExecScalar<T>(string sqlText)
        {
            object sonuc = null;
            using (SqlCeConnection cn = new SqlCeConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCeCommand cmd = new SqlCeCommand(sqlText, cn))
                {
                    sonuc = cmd.ExecuteScalar();
                }
                cn.Close();
            }
            if (sonuc != DBNull.Value)
                return (T)Convert.ChangeType(sonuc, typeof(T), null);
            else
                return (T)Cevir.DefaultSetting(typeof(T).ToString());
        }
        #endregion

        #region Schema Create

        public void CreateDb(string cnnStr)
        {
            using (SqlCeEngine eng = new SqlCeEngine(cnnStr))
            {
                eng.CreateDatabase();
            }
        }

        public void ValidateTable(Tablo entity)
        {
            using (SqlCeConnection cn = new SqlCeConnection(this.CnnStr))
            {
                cn.Open();
                using (var command = new SqlCeCommand())
                {
                    command.Connection = cn;

                    command.CommandText = string.Format("select count(*) from information_schema.tables where TABLE_NAME ='{0}'", entity.TabloAdi);
                    if (Cevir.Parse<int>(command.ExecuteScalar()) == 0)
                    {
                        CreateTable(entity);
                        return;
                    }

                    foreach (var field in Entity.Get.Tables[entity.TabloAdi].Fields)
                    {
                        var sql = string.Format("SELECT column_name, "  // 0
                                  + "data_type, "                       // 1
                                  + "character_maximum_length, "        // 2
                                  + "numeric_precision, "               // 3
                                  + "numeric_scale, "                   // 4
                                  + "is_nullable "
                                  + "FROM information_schema.columns "
                                  + "WHERE (table_name = '{0}' AND column_name = '{1}')",
                                  entity.TabloAdi, field.Value.dbAdi);

                        command.CommandText = sql;

                        using (var reader = command.ExecuteReader())
                        {
                            if (!reader.Read())
                            {
                                // field doesn't exist - we must create it
                                var alter = new StringBuilder(string.Format("ALTER TABLE [{0}] ", entity.TabloAdi));
                                alter.Append(string.Format("ADD {0} {1} {2}",
                                    field.Value.dbAdi,
                                    field.Value.dbType.ToSqlTypeString(),
                                    GetFieldCreationAttributes(field.Value, true)));

                                using (var altercmd = new SqlCeCommand(alter.ToString(), cn))
                                {
                                    altercmd.ExecuteNonQuery();
                                }
                            }
                            else
                            {
                                // TODO: verify field length, etc.
                            }
                        }
                    }
                }
                cn.Close();
            }
        }

        private string GetFieldCreationAttributes(FieldObject field, bool validateProcess)
        {
            StringBuilder sb = new StringBuilder();
            switch (field.dbType)
            {
                case DbType.String:
                    if (field.dbLenght > 0)
                    {
                        sb.AppendFormat("({0}) ", field.dbLenght);
                    }
                    else
                    {
                        sb.AppendFormat("({0}) ", 50);
                    }
                    break;
            }

            if (field.Identity)
            {
                sb.Append("IDENTITY ");
            }

            field.AllowsNulls = false;
            if (!validateProcess && !field.AllowsNulls)
            {
                sb.Append("NOT NULL ");
            }

            if (field.RequireUniqueValue)
            {
                sb.Append("UNIQUE ");
            }

            return sb.ToString();
        }

        public void CreateTable(Tablo entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendFormat("CREATE TABLE {0} (", entity.TabloAdi);

            int count = Entity.Get.Tables[entity.TabloAdi].Fields.Count;

            foreach (var field in Entity.Get.Tables[entity.TabloAdi].Fields)
            {
                sql.AppendFormat("[{0}] {1} {2}",
                    field.Value.dbAdi,
                    Methods.ToSqlTypeString(field.Value.dbType),
                    GetFieldCreationAttributes(field.Value, false));

                if (--count > 0) sql.Append(", ");
            }

            sql.Append(")");
            using (SqlCeConnection cn = new SqlCeConnection(this.CnnStr))
            {
                cn.Open();
                using (SqlCeCommand command = new SqlCeCommand(sql.ToString(), cn))
                {
                    int i = command.ExecuteNonQuery();
                }

                foreach (var field in Entity.Get.Tables[entity.TabloAdi].Indexes)
                {
                    string idxsql = string.Empty;
                    if (field.Value.IndexType == IndexObject.Tip.Index)
                        idxsql = string.Format("CREATE INDEX ORM_IDX_{0}_{1} ON {0}({2})",
                                entity.TabloAdi,
                                field.Key,
                                field.Value.Fields);
                    else
                        idxsql = string.Format("ALTER TABLE {0} ADD CONSTRAINT PK_IDX_{0}_{1} PRIMARY KEY ({2}) ",
                                entity.TabloAdi,
                                field.Key,
                                field.Value.Fields);

                    using (SqlCeCommand command = new SqlCeCommand(idxsql, cn))
                    {
                        int i = command.ExecuteNonQuery();
                    }
                }
                cn.Close();
            }
        }

        #endregion
    }
}
